# 导入 xlrd 库
import xlrd
import json
import pymongo


def readFile(filename):
    # 打开刚才我们写入的 test_w.xls 文件 工作
    workbook = xlrd.open_workbook(filename)
    # 获取并打印 sheet 数量
    # print( "sheet 数量:", workbook.nsheets)
    # # 获取并打印 sheet 名称
    # print("sheet 名称:", workbook.sheet_names())
    # 根据 sheet 索引获取内容
    li = []
    for index in range(workbook.nsheets):
        sheet = workbook.sheet_by_index(index)
        # 获取并打印该 sheet 行数和列数
        # print(u"sheet %s 共 %d 行 %d 列" % (sheet.name, sheet.nrows, sheet.ncols))
        # 获取并打印某个单元格的值
        if '主轴承装配' in sheet.cell_value(2, 1):
            li.append(readUZ(sheet))
            # print("图纸编号", sheet.cell_value(2, 1))
    return li

# 主轴承装配根据提取数据
def readUZ(sheet):
    zhuzhoucheng = {
        "文件名称": sheet.cell_value(0, 1),
        "图号": sheet.cell_value(1, 1),
        "图纸名称": sheet.cell_value(2, 1),
        "产品型号": sheet.cell_value(3, 1),
        "材料名称": sheet.cell_value(4, 1),
        "数量": sheet.cell_value(5, 1),
        "重量": sheet.cell_value(6, 1),
        "设计日期": sheet.cell_value(7, 1),
        "子图": []
    }
    # print("图纸编号", sheet.cell_value(2, 1))
    # print('明细表', sheet.nrows)

    for i in range(9, sheet.nrows):
        row = []
        for j in range(9):
            row.append(sheet.cell_value(i, j))
        zitu = {
            '序号': row[0],
            '图号': row[1],
            '名称': row[2],
            '数量': row[3],
            '材料': row[4],
            '单件': row[5],
            '总计': row[6],
            '备注': row[7],
            '来源': row[8],
        }
        zhuzhoucheng["子图"].append(zitu)
    zhuzhouchengJson = json.dumps(zhuzhoucheng, ensure_ascii=False)
    # print(zhuzhouchengJson)
    # return zhuzhouchengJson
    return zhuzhoucheng



def save2db():
    li = readFile('./主轴承完整nodes.xls')
    print(len(li))
    myclient = pymongo.MongoClient('mongodb://localhost:27017/')
    mydb = myclient['pctt']
    mycol = mydb['主轴承装配']

    x= mycol.insert_many(li)
    print(x.inserted_ids)

if __name__ == '__main__':
    # readFile('./主轴承完整nodes.xls')
    # save2db()
    sheetAll